CREATE EXTERNAL TABLE `jms_dm.dm_cn_departure_transfer_timely_route_agg_dt`(
                                                                               `start_code` string COMMENT '始发地 发件中心',
                                                                               `start_name` string COMMENT '始发地 发件中心',
                                                                               `order_source_code` string COMMENT '订单来源:1.桃花岛，2.抖音-紫金山，3.快手-逍遥峰，4.其他',
                                                                               `order_source_name` string COMMENT '订单来源',
                                                                               `center_type` int COMMENT '中心类型,1始发2中转',
                                                                               `start_agent_code` string COMMENT '发件代理区',
                                                                               `start_agent_name` string COMMENT '发件代理区',
                                                                               `send_account` bigint COMMENT '发件票数',
                                                                               `route_lack_account` bigint COMMENT '静态路由缺失票数',
                                                                               `transfer_intime_account` bigint COMMENT '转运及时票数',
                                                                               `transfer_intime_rate` double COMMENT '出港转运及时率',
                                                                               `without_arrival_account` bigint COMMENT '无到达时间票数',
                                                                               `error_send_account` bigint COMMENT '错发票数',
                                                                               `opp_delay_account` bigint COMMENT '操作延误票数',
                                                                               `send_delay_account` bigint COMMENT '发车晚点票数',
                                                                               `shipment_delay_account` bigint COMMENT '任务单问题票数',
                                                                               `stop_delay_account` bigint COMMENT '经停点问题票数',
                                                                               `all_line_transfer_intime_account` bigint COMMENT '全链路-转运及时票数',
                                                                               `all_line_transfer_intime_rate` double COMMENT '全链路-转运及时率',
                                                                               `shipment_error_account` bigint COMMENT '任务单错用票数'
)
    COMMENT '出港转运及时率汇总（基于静态路由）'
    PARTITIONED BY (
        `dt` string COMMENT '日期分区')
    ROW FORMAT SERDE
        'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
    STORED AS INPUTFORMAT
        'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'
        OUTPUTFORMAT
            'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
    LOCATION
        '/dw/hive/jms_dm.db/external/dm_cn_departure_transfer_timely_route_agg_dt'

ALTER TABLE jms_dm.dm_cn_departure_transfer_timely_route_agg_dt add COLUMNS(
temporary_dispatch_account BIGINT COMMENT '临时调度票量',
reserve_route_account BIGINT COMMENT '备用路由票数',
epidemic_hoarding_account BIGINT COMMENT '疫情囤货票数',
store_count int COMMENT '留仓票数',
express_type_code  string COMMENT '产品类型code',
express_type_name  string COMMENT '产品类型名称'
)cascade;

ALTER TABLE jms_dm.dm_cn_departure_transfer_timely_route_agg_dt add COLUMNS(
reserved_seconds BIGINT COMMENT '操作时长',
start_provider_id  string COMMENT '始发省份',
start_provider_desc  string COMMENT '始发省份'
)cascade;